﻿using Api.Common;
using Api.DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Api.DAL
{
    /// <summary>
    /// 下载附件
    /// </summary>
    public partial class evidence_shenpi_attach
    {
        private string databaseprefix = "eazy_"; //数据库表名前缀
        public evidence_shenpi_attach()
        { }
        #region Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "evidence_shenpi_attach");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public void UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "evidence_shenpi_attach set " + strValue);
            strSql.Append(" where id=" + id);
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 查找不存在的文件并删除已删除的附件及数据
        /// </summary>
        public void DeleteList(SqlConnection conn, SqlTransaction trans, List<Models.evidence_shenpi_attach> models, int article_id)
        {
            StringBuilder idList = new StringBuilder();
            if (models != null)
            {
                foreach (Models.evidence_shenpi_attach modelt in models)
                {
                    if (modelt.id > 0)
                    {
                        idList.Append(modelt.id + ",");
                    }
                }
            }
            string id_list = Utils.DelLastChar(idList.ToString(), ",");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id from " + databaseprefix + "evidence_shenpi_attach where article_id=" + article_id);
            if (!string.IsNullOrEmpty(id_list))
            {
                strSql.Append(" and id not in(" + id_list + ")");
            }
            DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                int rows = DbHelperSQL.ExecuteSql(conn, trans, "delete from " + databaseprefix + "evidence_shenpi_attach where id=" + dr["id"].ToString()); //删除数据库
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Models.evidence_shenpi_attach GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 id,article_id,instance_id,title,guige,danwei,number,remark,add_time from " + databaseprefix + "evidence_shenpi_attach ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            Models.evidence_shenpi_attach model = new Models.evidence_shenpi_attach();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["instance_id"].ToString() != "")
                {
                    model.instance_id = int.Parse(ds.Tables[0].Rows[0]["instance_id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["article_id"].ToString() != "")
                {
                    model.article_id = int.Parse(ds.Tables[0].Rows[0]["article_id"].ToString());
                }
                model.title = ds.Tables[0].Rows[0]["title"].ToString();
                model.guige = ds.Tables[0].Rows[0]["guige"].ToString();
                model.danwei = ds.Tables[0].Rows[0]["danwei"].ToString();
                model.number = ds.Tables[0].Rows[0]["number"].ToString();
                model.remark = ds.Tables[0].Rows[0]["remark"].ToString();
                if (ds.Tables[0].Rows[0]["add_time"].ToString() != "")
                {
                    model.add_time = DateTime.Parse(ds.Tables[0].Rows[0]["add_time"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<Models.evidence_shenpi_attach> GetList(int article_id)
        {
            List<Models.evidence_shenpi_attach> modelList = new List<Models.evidence_shenpi_attach>();

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,article_id,instance_id,title,guige,danwei,number,remark,add_time ");
            strSql.Append(" FROM " + databaseprefix + "evidence_shenpi_attach ");
            strSql.Append(" where article_id=" + article_id);
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];

            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                Models.evidence_shenpi_attach model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new Models.evidence_shenpi_attach();
                    if (dt.Rows[n]["id"] != null && dt.Rows[n]["id"].ToString() != "")
                    {
                        model.id = int.Parse(dt.Rows[n]["id"].ToString());
                    }
                    if (dt.Rows[n]["instance_id"] != null && dt.Rows[n]["instance_id"].ToString() != "")
                    {
                        model.instance_id = int.Parse(dt.Rows[n]["instance_id"].ToString());
                    }
                    if (dt.Rows[n]["article_id"] != null && dt.Rows[n]["article_id"].ToString() != "")
                    {
                        model.article_id = int.Parse(dt.Rows[n]["article_id"].ToString());
                    }
                    if (dt.Rows[n]["title"] != null && dt.Rows[n]["title"].ToString() != "")
                    {
                        model.title = dt.Rows[n]["title"].ToString();
                    }
                    if (dt.Rows[n]["guige"] != null && dt.Rows[n]["guige"].ToString() != "")
                    {
                        model.guige = dt.Rows[n]["guige"].ToString();
                    }
                    if (dt.Rows[n]["danwei"] != null && dt.Rows[n]["danwei"].ToString() != "")
                    {
                        model.danwei = dt.Rows[n]["danwei"].ToString();
                    }
                    if (dt.Rows[n]["number"] != null && dt.Rows[n]["number"].ToString() != "")
                    {
                        model.number = dt.Rows[n]["number"].ToString();
                    }
                    if (dt.Rows[n]["remark"] != null && dt.Rows[n]["remark"].ToString() != "")
                    {
                        model.remark = dt.Rows[n]["remark"].ToString();
                    }
                    if (dt.Rows[0]["add_time"].ToString() != "")
                    {
                        model.add_time = DateTime.Parse(dt.Rows[0]["add_time"].ToString());
                    }
                    modelList.Add(model);
                }
            }
            return modelList;
        }

        /// <summary>
        /// 得到数据列表
        /// </summary>
        public DataTable GetList_I(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select * from " + databaseprefix + "evidence_shenpi_attach ");
            strSql.Append(" where instance_id=" + id);
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        #endregion
    }
}
